﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using KOPLibrary.DataManagement;
using KOPLibrary.Forms;
using System.Threading;
using System.Text.RegularExpressions;



namespace KOPLibrary
{
    public class ExcelManager
    {
        private Microsoft.Office.Interop.Excel.Application app;

        public ExcelManager(Microsoft.Office.Interop.Excel.Application app)
        {
            this.app = app;

                
        }

        public ExcelManager()
        {
            app = new Application();
        }

        public void Close()
        {
            app.Quit();
            app = null;
        }


        public Microsoft.Office.Interop.Excel.Workbook CreateExcelWorkbook()
        {
            return CreateExcelWorkbook(string.Empty);
        }

        public Microsoft.Office.Interop.Excel.Workbook CreateExcelWorkbook(string baseTemplate)
        {
            object templatePath = baseTemplate;
            if (string.IsNullOrEmpty(baseTemplate))
            {
            }

            var newDoc = app.Workbooks.Add(templatePath);

            return newDoc;
        }

        private List<string> GetFilteredRows(string address)
        {
            List<string> result = new List<string>();
            Regex rex;
            MatchCollection mc;
            if (!address.Contains(":"))
            {                
                 rex = new Regex("[0-9]+");
                 mc = rex.Matches(address);
                foreach (Match item in mc)
                    result.Add(string.Format("{0},{1}:{2},{3}",item.Value,int.MinValue,item.Value,int.MaxValue));
                return result;
            }
            else
            {
                 rex = new Regex("[0-9]+,[0-9]+:[0-9]+,[0-9]+");
                 mc = rex.Matches(address);
                foreach (Match item in mc)
                    result.Add(item.Value);
                return result;
            }
            
        }

        private AbstractData RetrieveFilteredData(int maxRows, bool firstRowHeader)
        {
            List<string> selection;
            List<string> used;
            int[] temp, temp2;            
            AbstractData data = new AbstractData();
            Microsoft.Office.Interop.Excel.Range selectionrange = ((Microsoft.Office.Interop.Excel.Range)app.Selection).SpecialCells(XlCellType.xlCellTypeVisible);
            string address = selectionrange.get_Address(ReferenceStyle: XlReferenceStyle.xlR1C1).Replace("R", string.Empty).Replace("C", ",");
            string address2 = ((Worksheet)app.ActiveSheet).UsedRange.SpecialCells(XlCellType.xlCellTypeVisible).get_Address(ReferenceStyle: XlReferenceStyle.xlR1C1).Replace("R", string.Empty).Replace("C", ",");
            used = GetFilteredRows(address2);
            if (used.Count < 1) return null;
            temp = GetPointsFromAddress(used[0]);
            int[] usedbounds = temp;
            selection = GetFilteredRows(address);
            foreach (string item in used)
            {
                temp = GetPointsFromAddress(item);
                usedbounds = GetUsableRangeFiltered(usedbounds, temp);
            }
            
            string[] templist = selection.ToArray();
            if (selection != used)
            {
                foreach (string item in templist)
                {
                    temp = GetPointsFromAddress(item);
                    temp2  = GetUsableRange(temp, usedbounds);
                    for (int i = 0; i < temp.Length; i++)
                    {
                        if (temp[i] != temp2[i] && templist.Length == 1)
                        {
                            selection.Remove(item);
                            break;
                        }
                    }
                }
            }
            if (selection.Count < 1) selection = used;
            temp = GetUsableRange(GetPointsFromAddress(selection[0]), usedbounds);
            int startindex;
            int columncount = (temp[3] - temp[1]) + 1;
            int startcol = temp[1], endcol = temp[3];
            int rowcount = (maxRows == 0 || maxRows > selection.Count) ? selection.Count : maxRows;
            if (firstRowHeader)
            {
                startindex = 1;
                for (int i = temp[1]; i <= temp[3]; i++)
                {
                    object o =((Microsoft.Office.Interop.Excel.Range)((Worksheet)app.ActiveSheet).UsedRange.SpecialCells(XlCellType.xlCellTypeVisible).Cells[temp[0],i]).Value2;
                    data.Columns.Add(o == null ? string.Format("Alan{0}", i) : o.ToString());
                }
            }
            else
            {
                startindex = 0;
                for (int i = 1; i <= columncount; i++)
                    data.Columns.Add("Alan" + i.ToString());
            }
            object[] temparray;
            for (int i = startindex; i < rowcount; i++)
            {
                temp = GetPointsFromAddress(selection[i]);
                temparray = new object[columncount];
                int ctr = 0;
                for (int j = startcol; j <= endcol; j++)
                {
                    object o = ((Microsoft.Office.Interop.Excel.Range)((Worksheet)app.ActiveSheet).UsedRange.Cells[temp[0], j]).Value2;
                    temparray[ctr] = o ?? string.Empty;
                    ctr++;
                }
                data.Add(temparray);
            }
            return data;
        }

        public AbstractData RetrieveData(int maxRows, bool firstRowHeader)
        {
            AbstractData data = new AbstractData();
            bool filtered = ((Worksheet)app.ActiveSheet).FilterMode;

            Microsoft.Office.Interop.Excel.Range filteredrange = ((Microsoft.Office.Interop.Excel.Range)app.Selection).SpecialCells(XlCellType.xlCellTypeVisible);
            string filteredaddress = filteredrange.get_Address(ReferenceStyle: XlReferenceStyle.xlR1C1).Replace("R", string.Empty).Replace("C", ",");
            if (filtered)
            {
                return RetrieveFilteredData(maxRows, firstRowHeader);
            }
            Microsoft.Office.Interop.Excel.Range selectionrange = ((Microsoft.Office.Interop.Excel.Range)app.Selection);
            string address = selectionrange.get_Address(ReferenceStyle: XlReferenceStyle.xlR1C1).Replace("R", string.Empty).Replace("C", ",");
            string address2 = ((Worksheet)app.ActiveSheet).UsedRange.get_Address(ReferenceStyle: XlReferenceStyle.xlR1C1).Replace("R", string.Empty).Replace("C", ",");
            int[] selection = GetPointsFromAddress(address);
            int[] usedrange = GetPointsFromAddress(address2);
            int[] usuableselection;
            for (int i = 0; i <= 3; i++)
            {
                if (usedrange[i] == int.MinValue || usedrange[i] == int.MaxValue)
                {
                    return null;
                }
            }
            bool test = false;
            for (int i = 0; i <= 2; i++)
            {
                if (i == 1) continue;
                if ((selection[i] == int.MinValue || selection[i] == int.MaxValue) && (selection[i + 1] == int.MinValue || selection[i + 1] == int.MaxValue))
                {
                    test = true;
                    break;
                }
            }
            if (test)
            {
                selectionrange = ((Worksheet)app.ActiveSheet).UsedRange;
                usuableselection = usedrange;
            }
            else { usuableselection = GetUsableRange(selection, usedrange); }
            int startindex;
            int columncount = (usuableselection[3] - usuableselection[1]) + 1;
            int usuablerowcount = (usuableselection[2] - usuableselection[0]) + 1;
            int rowcount;
            if (maxRows == 0)
            {
                rowcount = usuablerowcount;
            }
            else
            {
                if (firstRowHeader) maxRows++;
                if (maxRows > usuablerowcount) rowcount = usuablerowcount; else rowcount = maxRows;
            }

            if (firstRowHeader)
            {
                startindex = 2;
                for (int i = 1; i <= columncount; i++)
                {
                    object o = ((Microsoft.Office.Interop.Excel.Range)selectionrange.Cells[1, i]).Value2;
                    data.Columns.Add(o == null ? string.Format("Alan{0}", i) : o.ToString());
                }
            }
            else
            {
                startindex = 1;
                for (int i = 1; i <= columncount; i++)
                    data.Columns.Add("Alan" + i.ToString());
            }
            object[] temparray;
            for (int i = startindex; i <= rowcount; i++)
            {
                temparray = new object[columncount];
                for (int j = 1; j <= columncount; j++)
                {
                    object o = ((Microsoft.Office.Interop.Excel.Range)selectionrange.Cells[i, j]).Value2;
                    temparray[j - 1] = o ?? string.Empty;
                }
                data.Add(temparray);
            }
            return data;
        }


        private int[] GetUsableRange(int[] selection, int[] usedrange)
        {
            int[] temp = selection.ToArray();
            for (int i = 0; i <= 1; i++)
            {
                if (temp[i] < usedrange[i]) temp[i] = usedrange[i];
            }
            for (int i = 2; i <= 3; i++)
            {
                if (temp[i] > usedrange[i]) temp[i] = usedrange[i];
            }
            return temp;
        }
        private int[] GetUsableRangeFiltered(int[] selection, int[] usedrange)
        {
            int[] temp = selection.ToArray();
            for (int i = 0; i <= 1; i++)
            {
                if (temp[i] > usedrange[i]) temp[i] = usedrange[i];
            }
            for (int i = 2; i <= 3; i++)
            {
                if (temp[i] <usedrange[i]) temp[i] = usedrange[i];
            }
            return temp;
        }
        private int[] GetPointsFromAddress(string address)
        {
            if (address.IndexOf(":") == -1) return new int[] { int.MinValue, int.MinValue, int.MaxValue, int.MaxValue };
            int[] myarray = new int[4];
            int tempindex = address.IndexOf(":");
            string cell1 = address.Substring(0, tempindex).Trim();
            string cell2 = address.Substring(tempindex + 1, address.Length - (cell1.Length + 1)).Trim();
            tempindex = cell1.IndexOf(",") + 1;
            try
            {
                myarray[0] = int.Parse(cell1.Substring(0, cell1.IndexOf(",")));
            }
            catch { myarray[0] = int.MinValue; }
            try
            {
                myarray[1] = int.Parse(cell1.Substring(tempindex, cell1.Length - tempindex));
            }
            catch { myarray[1] = int.MinValue; }
            tempindex = cell2.IndexOf(",") + 1;
            try
            {
                myarray[2] = int.Parse(cell2.Substring(0, cell2.IndexOf(",")));
            }
            catch { myarray[2] = int.MaxValue; }
            try
            {
                myarray[3] = int.Parse(cell2.Substring(tempindex, cell2.Length - tempindex));
            }
            catch { myarray[3] = int.MaxValue; }
            if (address.IndexOf(",") == -1) return new int[] { myarray[1], int.MinValue, myarray[3], int.MaxValue };
            return myarray;
        }

        public Workbook OpenDocument(string fileName)
        {
            return app.Workbooks.Open(fileName);
        }

        public Workbook OpenDocument(string fileName, string password)
        {
            return app.Workbooks.Open(fileName, Password: password);
        }

        public static object OpenForSelectData()
        {
            Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = true;
            app.UserControl = true;
            var oBooks = app.Workbooks;
            System.Globalization.CultureInfo ciSave = Thread.CurrentThread.CurrentCulture;
            System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
            Thread.CurrentThread.CurrentCulture = ci;

            object result = null;

            if (app.Dialogs[XlBuiltInDialog.xlDialogOpen].Show())
            {
                result = app.ActiveWorkbook;
            }
            else
            {
                app.Quit();
            }

            Thread.CurrentThread.CurrentCulture = ciSave;
            return result;
        }

        public static AbstractData GetSelectedData(object book)
        {
            System.Globalization.CultureInfo ciSave = Thread.CurrentThread.CurrentCulture;
            System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
            Thread.CurrentThread.CurrentCulture = ci;
            Workbook wBook = (Workbook)book;
            ExcelManager manager = new ExcelManager(wBook.Application);
            AbstractData data = SelectExcelDataForm.Execute(manager, null);
            if (data != null)
                wBook.Close();
            Thread.CurrentThread.CurrentCulture = ciSave;
            return data;
        }

        public List<string> RetrieveColumns(string fileName)
        {
            List<string> res = new List<string>();
            System.Globalization.CultureInfo ciSave = Thread.CurrentThread.CurrentCulture;
            System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
            Thread.CurrentThread.CurrentCulture = ci;
            try
            {
                Workbook wBook = OpenDocument(fileName);
                if (wBook.ActiveSheet != null)
                {
                    Worksheet s = (Worksheet)wBook.ActiveSheet;
                    Range usedRange = s.UsedRange;
                    for (int i = 1; i <= usedRange.Columns.Count; i++)
                        res.Add(((Range)usedRange.Cells[1, i]).Text.ToString());
                }
            }
            finally
            {
                Thread.CurrentThread.CurrentCulture = ciSave;
            }

            return res;
        }
    }
}
